﻿
--当日注册套数
select COUNT(*) from Devices where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, RegisterDate)))
--当月注册套数
select COUNT(*) from Devices where MONTH(GETDATE())= Month(RegisterDate) and YEAR(GetDate())= YEAR(registerdate)
--当日故障车且有人关怀 （需要确定时间吗？）
select COUNT(distinct v.vehicleid) from Vehicles v 
		 join Faults f on v.VehicleID = f.VehicleID 
		 where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.ReceiveDate)))
				and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.FirstContactDate)))
				and f.FaultType = '故障'



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetFaultStatsByVehicleType]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select myfaulttype=1,vt.model,count(distinct f.vehicleid) IDCCount
			 from faults f
			 join Vehicles v on f.vehicleid = v.vehicleid
			 join VehicleType vt on v.vehicletypeid = vt.vehicletypeid
			 where f.faulttype='故障' 
				and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.ReceiveDate)))
			 group by model
	union		 
	select myfaulttype=0,vt.model,count(distinct f.vehicleid) IDCCount
			 from faults f
			 join Vehicles v on f.vehicleid = v.vehicleid
			 join VehicleType vt on v.vehicletypeid = vt.vehicletypeid
			 where f.faulttype='撞车' 
				and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.ReceiveDate)))
			 group by model
	union
	select myfaulttype=2,vt.model,count(distinct h.vehicleid) IDCCount
			 from vehiclemaintenancehistory h
			 join Vehicles v on h.vehicleid = v.vehicleid
			 join VehicleType vt on v.vehicletypeid = vt.vehicletypeid
			 where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, h.ReceiveDate)))
			 group by model

					
END



GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetDeclineStats]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF OBJECT_ID('tempdb..#tempMaintenance') IS NOT NULL DROP TABLE #tempMaintenance
	select v.vehicleid,case when count(v.VehicleID)>0 then 1 else 0 end maintenancecount
	into #tempMaintenance
	from VehicleMaintenanceHistory f join Vehicles v
	on f.VehicleID = v.VehicleID
	where	f.CloseReason like '%拒绝%' 
			and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.CloseDate)))
	group by v.VehicleID
	
	IF OBJECT_ID('tempdb..#tempFault') IS NOT NULL DROP TABLE #tempFault
	select v.vehicleid,case when count(v.VehicleID)>0 then 1 else 0 end faultcount
	into #tempFault
	from faults f join Vehicles v
	on f.VehicleID = v.VehicleID
	where	f.FaultType='故障' 
			and f.CloseReason like '%拒绝%' 
			and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.CloseDate)))
	group by v.VehicleID
	
	IF OBJECT_ID('tempdb..#tempCrash') IS NOT NULL DROP TABLE #tempCrash
	select v.vehicleid,count(v.VehicleID) crashcount
	into #tempCrash
	from faults f join Vehicles v
	on f.VehicleID = v.VehicleID
	where	f.FaultType='撞车' 
			and f.CloseReason like '%拒绝%' 
			and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.CloseDate)))
	group by v.VehicleID

	select vt.Model VehicleModel,IsNull(SUM(v.maintenancecount),0) NumOfDeclineMaintenance,
					IsNull(SUM(v.faultcount),0) NumOfDeclineFault,
					IsNull(SUM(v.crashcount),0) NumOfDeclineCrash
			from VehicleType vt 
			left join (select v.VehicleID,v.VehicleTypeID,
					IsNull(m.maintenancecount,0) maintenancecount,
					IsNull(t.crashcount,0) crashcount,
					IsNull(f.faultcount,0) faultcount 
					from vehicles v 
					left join #tempcrash t on v.VehicleID=t.vehicleid
					left join #tempfault f on v.VehicleID=f.vehicleid
					left join #tempmaintenance m on v.VehicleID=m.vehicleid) v 
			on vt.VehicleTypeID =v.VehicleTypeID
			group by vt.Model

END



GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetDayStatistic]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
		IF OBJECT_ID('tempdb..#tempStatistic') IS NOT NULL DROP TABLE #tempStatistic
		CREATE TABLE #tempStatistic (row int,Name1 nvarchar(50),value1 int,Name2 nvarchar(50),value2 int)

		--当日注册套数
		DECLARE @dayRegistration int;
		select @dayRegistration = COUNT(*) from Devices where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, RegisterDate)))

		--当月注册套数
		DECLARE @monthRegistration int;
		select @monthRegistration = COUNT(*) from Devices where MONTH(GETDATE())= Month(RegisterDate) and YEAR(GetDate())= YEAR(registerdate)
		insert into #tempStatistic (row,Name1,value1,Name2,value2) values (1,'当日注册套数',@dayRegistration,'当月注册套数',@monthRegistration)




		--当日事故车且有人关怀 （需要确定时间吗？）
		DECLARE @dayUrgentCare int;
		select @dayUrgentCare=COUNT(distinct v.vehicleid) from Vehicles v 
				 join Faults f on v.VehicleID = f.VehicleID 
				 where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.ReceiveDate)))
						and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.FirstContactDate)))
						and f.FaultType = '撞车'

		--当日事故车且超过30分钟无人关怀
		DECLARE @dayUrgentCareAfter30Min int;
		select @dayUrgentCareAfter30Min=count(distinct v.VehicleID) from Vehicles v
				join Faults f on v.VehicleID = f.VehicleID
				where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.ReceiveDate)))
				and f.FaultType = '撞车'
				and datediff(MINUTE,f.ReceiveDate, IsNull(f.FirstContactDate,getdate()))>30

		insert into #tempStatistic (row,Name1,value1,Name2,value2) values (
			2,
			'当日事故车报警且有人关怀的总数',@dayUrgentCare,
			'当日事故车报警超过30分钟无人关怀的总数',@dayUrgentCareAfter30Min)

		--当日保养车且有人关怀 （需要确定时间吗？）
		DECLARE @dayMaintenanceCare int;
		select @dayMaintenanceCare=COUNT(distinct v.vehicleid) from Vehicles v 
				 join VehicleMaintenanceHistory m on v.VehicleID = m.VehicleID 
				 where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, m.HappenDate)))
						and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, m.FirstContactDate)))
						


		--当日保养车且超过120分钟无人关怀
		DECLARE @dayMaintenanceCareAfter2hour int;
		select @dayMaintenanceCareAfter2hour=count(distinct v.VehicleID) from Vehicles v
				join VehicleMaintenanceHistory m on v.VehicleID = m.VehicleID
				where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, m.HappenDate)))
				and datediff(MINUTE,m.ReceiveDate, IsNull(m.FirstContactDate,getdate()))>120


		insert into #tempStatistic (row,Name1,value1,Name2,value2) values (
			3,
			'当日有需要保养且有客户关怀IDC的总数',@dayMaintenanceCare,
			'当日有需要保养但超过2小时还没有客户关怀IDC的总数',@dayMaintenanceCareAfter2hour)

		--当日故障车且有人关怀 （需要确定时间吗？）
		DECLARE @dayFaultCare int;
		select @dayFaultCare=COUNT(distinct v.vehicleid) from Vehicles v 
				 join Faults f on v.VehicleID = f.VehicleID 
				 where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.ReceiveDate)))
						and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.FirstContactDate)))
						and f.FaultType = '故障'

		--当日故障车且超过1小时无人关怀
		DECLARE @dayFaultCareAfter1hour int;
		select @dayFaultCareAfter1hour=count(distinct v.VehicleID) from Vehicles v
				join Faults f on v.VehicleID = f.VehicleID
				where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, f.ReceiveDate)))
				and f.FaultType = '故障'
				and datediff(MINUTE,f.ReceiveDate, IsNull(f.FirstContactDate,getdate()))>60


		insert into #tempStatistic (row,Name1,value1,Name2,value2) values (
			4,
			'当日有故障且有客户关怀IDC的总数',@dayFaultCare,
			'当日有故障但超过1小时还没有客户关怀IDC的总数',@dayFaultCareAfter1hour)

		DECLARE @dayDeclinedUser int;
		select @dayDeclinedUser=COUNT(*) from Vehicles v
			where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, v.LastContactTime)))
				and v.LastContactResult like '%拒绝%'
		insert into #tempStatistic (row,Name1,value1,Name2,value2) values (
			5,
			'当日拒绝回场的客户数量',@dayDeclinedUser,
			null,0)

		select * from #tempStatistic order by row 
		--delete Faults
END


GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetCareStats]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	truncate table CareStats
	insert into CareStats(FaultType,IDCSimNumber,CodeDescription,MyMonth,ReceiveDate,EffectiveReceiveDate,FirstContactDate,MyScore)
	select	f.faulttype FaultType,
		f.idcsimnumber IDCSimNumber,
		f.codedescription CodeDescription,
		ltrim(str(DATEPART(year,f.receivedate)))+'-'+ltrim(str(DATEPART(month,f.receivedate))) MyMonth,
		f.receivedate ReceiveDate,
		f.effectivereceivedate EffectiveReceiveDate,
		f.firstcontactdate FirstContactDate,
		case f.faulttype 
			when '故障' then  
				case 
					when f.firstcontactdate is null then 
						case when DATEDIFF(minute,f.effectivereceivedate,getdate())<1440 then 0
						else 2 --有故障的车辆超过1440分钟都没有去关怀的 计 2 分
					end
					when f.firstcontactdate is not null then 
						case when DATEDIFF(minute,f.effectivereceivedate,f.firstcontactdate)<1440 then 0 
						else 1 --有故障的车辆超过1440分钟才去关怀的 计 1 分
					end 
				end
			when '撞车' then  
				case 
					when f.firstcontactdate is null then 
						case when DATEDIFF(minute,f.effectivereceivedate,getdate())<10 then 0
						else 4 --有撞车的车辆超过10分钟都没有去关怀的 计 4 分
					end
					when f.firstcontactdate is not null then 
						case when DATEDIFF(minute,f.effectivereceivedate,f.firstcontactdate)<5 then 0 
						else 2 --有撞车的车辆超过5分钟才去关怀的 计 2 分
					end 
				end
			else null 
		end MyScore
		from faults f
 
		 insert into CareStats(FaultType,IDCSimNumber,CodeDescription,MyMonth,ReceiveDate,EffectiveReceiveDate,FirstContactDate,MyScore)
		 select '保养'  FaultType, 
				d.idcsimnumber IDCSimNumber,
				m.name CodeDescription,
				ltrim(str(DATEPART(year,v.receivedate)))+'-'+ltrim(str(DATEPART(month,v.receivedate))) MyMonth,
				v.receivedate ReceiveDate,
				v.effectivereceivedate EffectiveReceiveDate,
				v.firstcontactdate FirstContactDate,
				case 
					when v.firstcontactdate is null then 
						case when DATEDIFF(minute,v.effectivereceivedate,getdate())<1440 then 0
						else 1 --有保养的车辆超过1440分钟都没有去关怀的 计 1 分
					end
					when v.firstcontactdate is not null then 
						case when DATEDIFF(minute,v.effectivereceivedate,v.firstcontactdate)<1440 then 0 
						else 0.5 --有保养的车辆超过1440分钟才去关怀的 计 0.5 分
					end
					else null
				end MyScore
				from vehiclemaintenancehistory v
				join devices d on v.vehicleid = d.vehicleid
				join maintenancetype m on m.maintenancetypeid = v.maintenancetypeid
				
				
				select	 mymonth, 
				 sum(myscore) myscore
				 from CareStats C
				 group by mymonth
				
END


GO


